<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Getting database metadata</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="MySQL, Perl, DBI, metadata, tutorial, database, programming, development">
<meta name="description" content="In this part of the MySQL Perl tutorial, we work with database metadata.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container2">

<div id="wide_ad" class="ltow">
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<div class="content2">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Getting database metadata</h1>

<p>
Metadata is information about the data in the database. 
Metadata in MySQL contains information about the tables 
and columns, in which we store data. Number of rows affected 
by an SQL statement is a metadata. Number of rows and columns returned 
in a result set belong to metadata as well.
</p>

<div class="big_hor">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* big_horizontal */
google_ad_slot = "2904953388";
google_ad_width = 728;
google_ad_height = 90;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>
<br>


<table>
<thead>
<tr class="hdr"><th>Method name</th><th>Description</th></tr>
</thead>
<tbody>
<tr><td>column_info()</td><td>Provides information about columns</td></tr>
<tr class="gray"><td>table_info()</td><td>Provides information about tables</td></tr>
<tr><td>primary_key_info()</td><td>Provides information about primary keys in tables</td></tr>
<tr class="gray"><td>foreign_key_info()</td><td>Provides information about foreign keys in tables</td></tr>
</tbody>
</table>

<p>
The above table lists four Perl DBI methods, which are used to retrieve metadata. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->primary_key_info(undef, "mydb", "Cars");
my @ary = $sth->fetchrow_array();
      
print join(" ", @ary), "\n";

$sth->finish();
$dbh->disconnect();
</pre>

<p>
In the first example, we will find out information about a primary
key in the Cars table. 
</p>

<pre class="explanation">
my $sth = $dbh->primary_key_info(undef, "main", "Cars");
</pre>

<p>
The <code>primary_key_info()</code> returns an active statement handle 
that can be used to fetch information about columns that make up 
the primary key for a table. 
</p>

<pre class="explanation">
my @ary = $sth->fetchrow_array();
</pre>

<p>
From the statement handle, we retrieve the information. 
</p>

<pre>
$ ./pk_info.pl
 mydb Cars Id 1 PRIMARY
</pre>

<p>
From the output we can see, that there is a primary key in the Cars
table. The primary key is the first column, named Id. 
</p>


<hr class="btm">

<p>
Next we will print all rows from the Cars table with their column names.
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 8" );  
$sth->execute();

my $headers = $sth->{NAME};

my ($id, $name, $price) = @$headers;
printf  "%s %-10s %s\n", $id, $name, $price;

my $row;
while($row = $sth->fetchrow_hashref()) {
    printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}

$sth->finish();
$dbh->disconnect();
</pre>

<p>
We print the contents of the Cars table to the console. 
Now, we include the names of the columns too. The records are aligned
with the column names. 
</p>

<pre class="explanation">
my $headers = $sth->{NAME};
</pre>

<p>
We get the column names from the statement object. 
</p>

<pre class="explanation">
my ($id, $name, $price) = @$headers;
printf "%s %-10s %s\n", $id, $name, $price;
</pre>

<p>
The column names are printed to the console. We apply some formatting
with the printf function. 
</p>

<pre class="explanation">
my $row;
while($row = $sth->fetchrow_hashref()) {
    printf "%2d %-10s %d\n", $row->{Id}, $row->{Name}, $row->{Price};
}
</pre>

<p>
The data is retrieved, formatted and printed to the terminal. 
</p>

<pre>
$ ./column_names.pl
Id Name       Price
 1 Audi       52642
 2 Mercedes   57127
 3 Skoda      9000
 4 Volvo      29000
 5 Bentley    350000
 6 Citroen    21000
 7 Hummer     41400
 8 Volkswagen 21601
</pre>

<p>
Output. 
</p>

<hr class="btm">

<p>
In our last example related to the metadata, we will 
list all tables in the test.db database. 
</p>

<pre class="code">
#!/usr/bin/perl

use strict;
use DBI;

my $dbh = DBI->connect(          
    "dbi:mysql:dbname=mydb", 
    "user12",                          
    "34klq*",                          
    { RaiseError => 1 },         
) or die $DBI::errstr;

my @tables = $dbh->tables(); 

foreach my $table ( @tables ) {
     print "Table: $table\n"; 
}

$dbh->disconnect();
</pre>

<p>
The code example prints all available tables in the current database
to the terminal. 
</p>

<pre class="explanation">
my @tables = $dbh->tables();
</pre>

<p>
The table names are retrieved with the <code>tables()</code> method.
</p>

<pre>
$ ./list_tables.pl
Table: `mydb`.`Cars`
Table: `mydb`.`Friends`
Table: `mydb`.`Images`
</pre>

<p>
These were the tables on our system. 
</p>

<p>
In this part of the MySQL Perl tutorial, we have worked with database
metadata. 
</p>


<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>


<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified May 6, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>

